package com.and9.mchen.mcmusic.dao.impl;

import java.sql.Timestamp;
import java.text.MessageFormat;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.and9.mchen.mcmusic.constant.RelationTable;
import com.and9.mchen.mcmusic.dao.IBaseEntityDao;
import com.and9.mchen.mcmusic.dao.ISongDao;
import com.and9.mchen.mcmusic.entity.Lyric;
import com.and9.mchen.mcmusic.entity.MusicStyle;
import com.and9.mchen.mcmusic.entity.Song;
import com.and9.mchen.mcmusic.entity.Statistics;
import com.and9.mchen.mcmusic.exception.DBException;
import com.and9.mchen.mcmusic.exception.DaoException;
import com.and9.mchen.mcmusic.util.EntityHelper;
import com.and9.mchen.mcmusic.util.GEntityHelper;
import com.and9.mchen.mcmusic.util.QueryHelper;

/**
 * 
 * @author MC
 * @version  1.0
 */
public class SongDaoImpl implements ISongDao{

	private final static Log log = LogFactory.getLog(SongDaoImpl.class);

	public static String TABLEMANE="song_table";

	public int createOrUpdateSong(Song song, String... propertyNames)
			throws DaoException {
		if(GEntityHelper.isEmpty(song)){
			return -1;
		}
		
		String sqlStr=null;
		Object[] propertyValues=null;
		
		if(song.getId() == null || song.getId() <= 0){
			
			if(GEntityHelper.isEmpty(song.getUniqueId())){
				song.setUniqueId(EntityHelper.createUniqueId());
			}
			song.setCreateTime(new Timestamp(System.currentTimeMillis()));
			
			Object[] parentParams=QueryHelper.getFieldsVal(song.getParent(), IBaseEntityDao.BASEPROPERTYNAMES);
			Object[] songParams=QueryHelper.getFieldsVal(song, ISongDao.BASEPROPERTYNAMES);
			propertyValues=EntityHelper.groupArrays(parentParams, songParams);
			
			propertyNames=EntityHelper.groupArrays(IBaseEntityDao.BASEPROPERTYNAMES, ISongDao.BASEPROPERTYNAMES);
			sqlStr=QueryHelper.getInsertSQLByProperty(SongDaoImpl.TABLEMANE, propertyNames);
	
		}else {
			
			if(GEntityHelper.isEmpty(propertyNames) || 
					EntityHelper.containBaseEntityPropertyName(propertyNames) == true){
				propertyNames=ISongDao.BASEPROPERTYNAMES;
			}
			
			propertyValues=QueryHelper.getFieldsVal(song, propertyNames);
			
			sqlStr=QueryHelper.getUpdateSQL(SongDaoImpl.TABLEMANE, song, "id="+song.getId(), propertyNames);
			
		}
		
		try {
			return QueryHelper.update(sqlStr, propertyValues);
		} catch (DBException e) {
			log.error("[function: createOrUpdateSong]", e);
			throw new DaoException("create or update song faliure");
		}
		
	}

	public int[] createSongMusicStyleRelevanceBySong(Song song)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song) || 
				GEntityHelper.isEmpty(song.getMusicStyles())){
			return null;
		}
		
		Set<MusicStyle> musicStyles=(Set<MusicStyle>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(song.getMusicStyles());
		
		if(GEntityHelper.isEmpty(musicStyles)){
			return null;
		}
		
		Object[][] params = EntityHelper.initRelevanceParamsById(song, musicStyles);
		
		String sqlStr=QueryHelper.getInsertSQLByProperty(RelationTable.SONG_MUSIC_STYLE_TABLE, 
				"song_id", "music_style_id");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: createSongMusicTypeRelevanceBySong]", e);
			throw new DaoException("create song music type relevance by song failure");
		}
	}

	public int createSongStatisticsRelevanceBySong(Song song)
			throws DaoException {
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song.getStatistics())){
			return -1;
		}
		
		Statistics statistics=song.getStatistics();
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SongDaoImpl.TABLEMANE, "id=?", "statistics_id");
		
		try {
			//UPDATE  song_table   SET  statistics_id =? where id=?
			return QueryHelper.update(sqlStr, statistics.getId(), song.getId());
		} catch (DBException e) {
			log.error("[function: createSongStatisticsRelevanceBySong]", e);
			throw new DaoException("create song statistics relevance by song failure");
		}

	}
	
	
	public int deleteSongBySongId(long id) throws DaoException {

		if(id <= 0){
			return -1;
		}
		
		String sqlStr=QueryHelper.getDeleteSQL(SongDaoImpl.TABLEMANE, "id=?");
		
		try {
			return QueryHelper.update(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: deleteSongBySongId]", e);
			throw new DaoException(" delete song by id failure");
		}

	}

	public long getAllSongsCount() throws DaoException {

		Object[] objects=null;
		try {
			return QueryHelper.stat("SELECT COUNT(id) FROM "+SongDaoImpl.TABLEMANE, objects);
		} catch (DBException e) {
			log.error("[function: getAllSongsCount]", e);
			throw new DaoException("get all songs count failure");
		}
		
	}

	public Song getSongBySongId(long id) throws DaoException {
		
		if( id <= 0){
			return null;
		}
		
		try {
			String sqlStr=QueryHelper.getSelectSQL(SongDaoImpl.TABLEMANE, "id=?");
			return QueryHelper.read(Song.class, sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getSongBySongId]", e);
			throw new DaoException();
		}

	}

	public Song getSongBySongUniqueId(String uniqueId) throws DaoException {
		if(GEntityHelper.isEmpty(uniqueId)){
			return null;
		}
		
		String sqlStr=QueryHelper.getSelectSQL(SongDaoImpl.TABLEMANE, "uniqueId=?");
		
		try {
			return QueryHelper.read(Song.class, sqlStr, uniqueId);
		} catch (DBException e) {
			log.error("[function: getSongBySongUniqueId]", e);
			throw new DaoException("get song by song unique id failure");
		}
		
	}

	public List<Song> getSongsByAlbumId(long id, int pageNum, int pageCount, String orderBy)
			throws DaoException {
		if(id <= 0){
			return null;
		}
		
		if(GEntityHelper.isEmpty(orderBy)){
			orderBy=MessageFormat.format("{0}.isStick DESC, {0}.clickCount DESC", 
					StatisticsDaoImpl.TABLEMANE);
		}
		
		String sqlStr=MessageFormat.format(
				"SELECT DISTINCT {0}.* FROM {0}, {1}, {2} WHERE {0}.id={1}.song_id " +
						"AND {0}.statistics_id={2}.id AND {1}.album_id=? ORDER BY {3}", 
							SongDaoImpl.TABLEMANE, RelationTable.ALBUM_SONG_TABLE, 
								StatisticsDaoImpl.TABLEMANE, orderBy);
		
		try {
			return QueryHelper.query_slice(Song.class, sqlStr, pageNum, pageCount, id);
		} catch (DBException e) {
			log.error("[function: getSongsByAlbumId]", e);
			throw new DaoException("get songs by album id failure");
		}
		
	}

	public List<Song> getSongsByMusicStyleId(long id, int pageNum,
			int pageCount, String orderBy) throws DaoException {
		
		if(id <= 0){
			return null;
		}
		
		if(GEntityHelper.isEmpty(orderBy)){
			orderBy=MessageFormat.format("{0}.isStick DESC, {0}.clickCount DESC", 
					StatisticsDaoImpl.TABLEMANE);
		}
		
		String sqlStr=MessageFormat.format("SELECT DISTINCT {0}.* FROM {0}, {1}, {2} WHERE " +
				"{0}.id={1}.song_id AND {1}.music_style_id=? AND {0}.statistics_id={2}.id ORDER BY {3} ", 
					SongDaoImpl.TABLEMANE, RelationTable.SONG_MUSIC_STYLE_TABLE, 
						StatisticsDaoImpl.TABLEMANE, orderBy);
		
		try {
			return QueryHelper.query_slice(Song.class, sqlStr, pageNum, pageCount, id);
		} catch (DBException e) {
			log.error("[function: getSongsByMusicStyleId]", e);
			throw new DaoException("get songs by music style id failure");
		}
	}

	public List<Song> getSongsBySingerId(long id, int pageNum, int pageCount,
			String orderBy) throws DaoException {
		if(id <= 1L){
			return null;
		}
		
		if(GEntityHelper.isEmpty(orderBy)){
			orderBy=MessageFormat.format("{0}.isStick DESC, {0}.clickCount DESC", 
					StatisticsDaoImpl.TABLEMANE);
		}
		
		String sqlStr=MessageFormat.format("SELECT {0}.* FROM {0}, {1}, {2} WHERE " +
				"{0}.id={2}.song_id AND {0}.statistics_id={1}.id AND " +
					"{2}.album_id IN (SELECT DISTINCT {3}.id FROM {3} WHERE {3}.singer_id=? ) " +
						"ORDER BY {4}", 
						SongDaoImpl.TABLEMANE, StatisticsDaoImpl.TABLEMANE, 
							 RelationTable.ALBUM_SONG_TABLE, AlbumDaoImpl.TABLEMANE, orderBy);
		
		try {
			return QueryHelper.query_slice(Song.class, sqlStr, pageNum, pageCount, id);
		} catch (DBException e) {
			log.error("[function: getSongsBySingerId]", e);
			throw new DaoException("get songs by singer id failure");
		}

	}

	public long getSongsCountByAlbumId(long id) throws DaoException {
		
		if(id <= 0){
			return -1;
		}
		
		String sqlStr=MessageFormat.format(
				"SELECT COUNT({0}.id) FROM {0}, {1} WHERE {0}.id={1}.song_id AND {1}.album_id=?", 
					SongDaoImpl.TABLEMANE, RelationTable.ALBUM_SONG_TABLE);
		
		try {
			//SELECT COUNT(song_table.id) FROM song_table, album_song_table WHERE 
			//song_table.id=album_song_table.song_id AND album_song_table.album_id=?
			return QueryHelper.stat(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getSongsCountByAlbumId]", e);
			throw new DaoException("get songs count by album id failure");
		}
		
	}

	public long getSongsCountByMusicStyleId(long id) throws DaoException {
	
		if(id <= 0){
			return -1;
		}
		
		String sqlStr=MessageFormat.format("SELECT COUNT({0}.id) FROM {0}, {1} WHERE " +
				"{0}.id={1}.song_id AND {1}.music_style_id=?", 
					SongDaoImpl.TABLEMANE, RelationTable.SONG_MUSIC_STYLE_TABLE);
		
		try {
			return QueryHelper.stat(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getSongsCountByMusicStyleId]", e);
			throw new DaoException("get songs count by music style id failure");
		}

	}

	public long getSongsCountBySingerId(long id) throws DaoException {
		
		if(id <= 0){
			return -1;
		}
		
		if(id == 1){
			return 0;
		}
		
		String sqlStr=MessageFormat.format("SELECT COUNT({0}.id) FROM {0}, {1} WHERE " +
				"{0}.id={1}.song_id AND {1}.album_id IN(SELECT DISTINCT {2}.id FROM {2} WHERE " +
					"{2}.singer_id=? )", SongDaoImpl.TABLEMANE, RelationTable.ALBUM_SONG_TABLE, 
						AlbumDaoImpl.TABLEMANE);
		
		try {
			return QueryHelper.stat(sqlStr, id);
		} catch (DBException e) {
			log.error("[function: getSongsCountBySingerId]", e);
			throw new DaoException("get songs count by singer id failure");
		}
		
	}

	public int[] removeSongMusicStyleRelevanceBySong(Song song)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song) || 
				GEntityHelper.isEmpty(song.getMusicStyles())){
			return null;
		}
		
		Set<MusicStyle> musicStyles=(Set<MusicStyle>) GEntityHelper.findAllIdAndUniqueIdIsNotNull(song.getMusicStyles());
		
		if(GEntityHelper.isEmpty(musicStyles)){
			return null;
		}
		
		Object[][] params = EntityHelper.initRelevanceParamsById(song, musicStyles);
		
		String sqlStr=QueryHelper.getDeleteSQL(RelationTable.SONG_MUSIC_STYLE_TABLE, 
				"song_id=? AND music_style_id=? ");
		
		try {
			return QueryHelper.batch(sqlStr, params);
		} catch (DBException e) {
			log.error("[function: createSongMusicTypeRelevanceBySong]", e);
			throw new DaoException("create song music type relevance by song failure");
		}
	}

	public int removeSongStatisticsRelevanceBySong(Song song)
			throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song)){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SongDaoImpl.TABLEMANE, "id=?", "statistics_id");
		
		try {
			//UPDATE  song_table   SET  statistics_id =? where id=?
			return QueryHelper.update(sqlStr, 1L, song.getId());
		} catch (DBException e) {
			log.error("[function: removeSongStatisticsRelevanceBySong]", e);
			throw new DaoException("remove song statistics relevance by song failure");
		}

	}

	public List<Song> getSongs(String sql, int pageNum, int pageCount,
			Object... params) throws DaoException {
		if(GEntityHelper.isEmpty(sql)){
			return null;
		}
		
		try {
			return QueryHelper.query_slice(Song.class, sql, pageNum, pageCount, params);
		} catch (DBException e) {
			log.error("[function: getSongs]", e);
			throw new DaoException("get songs by sql failure");
		}
	
	}

	public int createSongLyricRelevanceBySong(Song song) throws DaoException {
		
		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song) || 
				!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song.getLyric())){
			return -1;
		}
		
		Lyric lyric=song.getLyric();
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SongDaoImpl.TABLEMANE, "id=?", "lyric_id");
		
		try {
			return QueryHelper.update(sqlStr, lyric.getId(), song.getId());
		} catch (DBException e) {
			log.error("[function: createSongLyricRelevanceBySong]", e);
			throw new DaoException("create song lyric relevance by song failure");
		}
		
	}

	public int removeSongLyricRelevanceBySong(Song song) throws DaoException {

		if(!GEntityHelper.checkIDAndUniqueIdWhetherIegal(song)){
			return -1;
		}
		
		String sqlStr=QueryHelper.getUpdateSQLByProperty(SongDaoImpl.TABLEMANE, "id=?", "lyric_id");
		
		try {
			
			return QueryHelper.update(sqlStr, 1L, song.getId());
		} catch (DBException e) {
			log.error("[function: removeSongLyricRelevanceBySong]", e);
			throw new DaoException("remove song lyric relevance by song failure");
		}
		
	}

}
